/*
{
"状态":1,
"名称":"获取-数据库表资料",
"作者":"陈泽椿",
"创建时间":"2018-07-17",
"功能":"获取-数据库表资料",
"log": [
	{"date":"06-01" , "dev" :"hzhh" , "content" : ""}
]
}
*/

declare @table_name nvarchar(500)
set @table_name = '{table_name}'


CREATE TABLE #Tmp
(
	id INT,
	pid INT,
	table_name NVARCHAR(400),
	describe NVARCHAR(500),
	remarks NVARCHAR(1000)
)

IF ISNULL(@table_name,'')=''
BEGIN 
	INSERT INTO #Tmp(id,pid,table_name,describe,remarks)
	SELECT 10000000+ROW_NUMBER() OVER(ORDER BY S.table_name) AS table_key,0,table_name,S.describe,remarks   FROM 
	(
	  SELECT DISTINCT project AS table_name,project AS describe,'' As remarks FROM dbo.y_table
	) S

	INSERT INTO #Tmp(id,pid,table_name,describe,remarks)
	SELECT A.table_key,#Tmp.id,A.table_name,A.describe,A.remarks FROM y_table A LEFT JOIN #Tmp ON #Tmp.table_name = A.project
END 
ELSE
BEGIN
	SET @table_name='%'+@table_name+'%'
	INSERT INTO #Tmp(id,pid,table_name,describe,remarks)
	SELECT 10000000+ROW_NUMBER() OVER(ORDER BY S.table_name) AS table_key,0,table_name,S.describe,remarks   FROM 
	(
	  SELECT DISTINCT project AS table_name,project AS describe,'' As remarks FROM dbo.y_table WHERE table_name LIKE @table_name OR describe LIKE @table_name
	) S

	INSERT INTO #Tmp(id,pid,table_name,describe,remarks)
	SELECT A.table_key,#Tmp.id,A.table_name,A.describe,A.remarks FROM y_table A LEFT JOIN #Tmp ON #Tmp.table_name = A.project
	WHERE A.table_name LIKE @table_name OR A.describe LIKE @table_name
END   

IF NOT EXISTS(SELECT * FROM #Tmp)
BEGIN
	INSERT INTO #Tmp(id,pid,table_name,describe,remarks)
	values(-1,0,N'查无此表',N'查无此表','')
END  


SELECT * FROM #Tmp order by table_name
DROP TABLE #Tmp